import xlrd
import pymysql
from datetime import datetime

from self import self
from xlrd import xldate_as_tuple

"连接数据库"


class robox_info_import:
    self.need_update = '''
    select use_people,delivery_time,robot_id,zeno_alias,robox_model,robot_model,sequence_sn,
    cmiit_id,usb_netcard_mac,lte_ssid,lte_passwd,sim_number,sim_active_time,sim_stop_time,
    used_record,used_charge,is_auto_xunhang,numbers,maintenance_record,other_info from robox_info_import where robot_id  not in (select robot_id from myapp3_robox_info)
    '''
    self.update_sql = "call robox_info_update()"
    self.sql_auto_init = "call Myapp3_Robox_Info_Id_Auto_Init()"
    self.query1 = "truncate table robox_info_import;"
    self.query2 = '''insert ignore into robox_info_import
    (use_people,delivery_time,robot_id,zeno_alias,robox_model,robot_model,sequence_sn,
    cmiit_id,usb_netcard_mac,lte_ssid,lte_passwd,sim_number,sim_active_time,sim_stop_time,
    used_record,used_charge,is_auto_xunhang,numbers,maintenance_record,other_info) 
    values (%s, %s, %s,%s,%s,%s,%s,%s, %s, %s,%s,%s,%s,%s,%s, %s, %s,%s,%s,%s)   
    '''

    @staticmethod
    def real_import():
        db = pymysql.connect('192.168.28.110', 'root', '1234qwer!', 'robox_web')
        # db = pymysql.connect('127.0.0.1', 'root', '1234qwer!', 'sunguilou')
        cursor = db.cursor()
        File = xlrd.open_workbook("C:/Users/sunguilou/Desktop/test1.xlsx")
        sheet = File.sheet_by_index(0)
        nrows = sheet.nrows
        cursor.execute(self.query1)
        for i in range(2, nrows):
            # try:
            use_people = sheet.cell_value(i, 3)
            delivery_time = sheet.cell_value(i, 2)
            robot_id = sheet.cell_value(i, 4)
            zeno_alias = sheet.cell_value(i, 5)
            robox_model = sheet.cell_value(i, 6)
            robot_model = sheet.cell_value(i, 7)
            sequence_sn = sheet.cell_value(i, 8)
            cmiit_id = sheet.cell_value(i, 9)
            usb_netcard_mac = sheet.cell_value(i, 10)
            lte_ssid = sheet.cell_value(i, 11)
            lte_passwd = sheet.cell_value(i, 12)
            sim_number = sheet.cell_value(i, 13)
            sim_active_time = sheet.cell_value(i, 14)
            sim_stop_time = sheet.cell_value(i, 15)
            used_record = sheet.cell_value(i, 16)
            used_charge = sheet.cell_value(i, 17)
            is_auto_xunhang = sheet.cell_value(i, 18)
            numbers = sheet.cell_value(i, 19)
            maintenance_record = sheet.cell_value(i, 20)
            other_info = sheet.cell_value(i, 21)
            if use_people and delivery_time and robot_id:
                date1 = datetime(*xldate_as_tuple(round(delivery_time), 0))
                delivery_time = date1.strftime('%Y-%m-%d ')
                if sim_active_time:
                    date2 = datetime(*xldate_as_tuple(round(sim_active_time), 0))
                    sim_active_time = date2.strftime('%Y-%m-%d ')
                if sim_stop_time:
                    date3 = datetime(*xldate_as_tuple(round(sim_stop_time), 0))
                    sim_stop_time = date3.strftime('%Y-%m-%d ')
                values = (use_people, delivery_time, robot_id, zeno_alias, robox_model, robot_model, sequence_sn,
                          cmiit_id, usb_netcard_mac, lte_ssid, lte_passwd, sim_number, sim_active_time, sim_stop_time,
                          used_record, used_charge, is_auto_xunhang, numbers, maintenance_record, other_info)
                cursor.execute(self.query2, values)
                print(self.query2, values)
        # except:
        #     print("error at ", i)
        #     continue

        number = cursor.execute(self.need_update)
        ans = cursor.fetchall()
        print('新增条数:' + str(number))
        for i in ans:
            print(i)
        cursor.execute(self.update_sql)
        # cursor.execute(self.sql_auto_init)
        cursor.close()
        db.commit()
        db.close()

robox_info_import.real_import()